﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using AudioCore.Common;
using System.Data;

namespace AudioCore.DAO
{
    internal class CategoryArticleDao : BaseDao
    {
        internal CategoryArticleDao()
            : base("CategoryArticle", "CategoryArticleID")
        {
            db = DatabaseFactory.CreateDatabase(Constant.DATABASE);
        }

        internal CategoryArticleDao(string viewName)
            : base(viewName, "CategoryArticleID")
        {
            db = DatabaseFactory.CreateDatabase(Constant.DATABASE);
        }

        internal int DeleteItem(int categoryID, int articleID)
        {
            string sql = "Delete from CategoryArticle where CategoryID = {0} AND ArticleID = {1}";
            sql = string.Format(sql, categoryID, articleID);
            return (int)db.ExecuteScalar(CommandType.Text, sql);
        }

        internal DataTable GetExtendedItemByArticleID(int articleID)
        {
            object[] param = new object[] { articleID };
            return db.ExecuteDataSet("sp_AUDIO_GetCategoryArticleByArticleID", param).Tables[0];
        }

        internal int UpdateSortOrder(int categoryID, int articleID, DateTime sortOrder)
        {
            string sql = "Update CategoryArticle set SortOrder = '{0}' where CategoryID = {1} AND ArticleID = {2}";
            sql = string.Format(sql, sortOrder, categoryID, articleID);
            return (int)db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal DataTable GetMappingCategoryArticle(int articleID)
        {
            string sql = "  SELECT C.*, A.CategoryArticleID, A.ArticleID "
                        + " FROM Category AS C "
                        + " LEFT JOIN CategoryArticle AS A ON A.CategoryID = C.CategoryID AND A.ArticleID = {0} ";
            sql = string.Format(sql, articleID);
            return db.ExecuteDataSet(CommandType.Text, sql).Tables[0];
        }
    }
}
